import pymysql
import socket
import datetime
import os
import json
import csv
import numpy as np
from collections import defaultdict


DB_CONFIG = {
    'host': '172.16.30.54',
    'port': 3306,
    'user': 'root',
    'password': 'IPS2025a%',
    'database': 'iip_numerical_algorithm_mini_program',
    'cursorclass': pymysql.cursors.DictCursor
}


def get_user_features():
    """
    统计用户数据特征并保存到CSV文件
    """
    # 连接数据库
    connection = pymysql.connect(**DB_CONFIG)
    try:
        with connection.cursor() as cursor:
            # 查询1: 统计用户登录信息
            login_sql = """
            SELECT
                COALESCE(JSON_EXTRACT(extra_param, '$.openid'), '-1') as openid,
                COALESCE(JSON_EXTRACT(extra_param, '$.deviceid'), '-1') as deviceid,
                MAX(CASE WHEN page_Id = 'event_firstTracking' THEN event_time END) as user_last_login_time,
                COUNT(CASE WHEN page_Id = 'event_firstTracking' AND event_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as user_login_count_7d,
                MAX(CASE WHEN page_Id = 'event_firstTracking' AND event_time >= DATE_SUB(NOW(), INTERVAL 3 DAY) THEN 1 ELSE 0 END) as user_is_login_3d
            FROM
                tracking_data
            WHERE
                page_Id = 'event_firstTracking'
                AND extra_param IS NOT NULL
                AND JSON_VALID(extra_param) = 1
            GROUP BY
                openid, deviceid
            """
            cursor.execute(login_sql)
            login_results = cursor.fetchall()
            login_dict = {(row['openid'].strip('"'), row['deviceid'].strip('"')): row for row in login_results}

            # 查询2: 获取用户点击事件
            click_sql = """
            SELECT
                COALESCE(JSON_EXTRACT(extra_param, '$.openid'), '-1') as openid,
                COALESCE(JSON_EXTRACT(extra_param, '$.deviceid'), '-1') as deviceid,
                event_time,
                event_type,
                COALESCE(JSON_EXTRACT(extra_param, '$.articleId'), JSON_EXTRACT(extra_param, '$.videoId'), '-1') as content_id,
                HOUR(event_time) as click_hour
            FROM
                tracking_data
            WHERE
                event_type LIKE '%click%'
                AND extra_param IS NOT NULL
                AND JSON_VALID(extra_param) = 1
            ORDER BY
                openid, deviceid, event_time
            """
            cursor.execute(click_sql)
            click_results = cursor.fetchall()

            # 处理点击事件数据
            user_click_data = defaultdict(list)
            for row in click_results:
                openid = row['openid'].strip('"')
                deviceid = row['deviceid'].strip('"')
                user_key = (openid, deviceid)
                user_click_data[user_key].append({
                    'event_time': row['event_time'],
                    'content_id': row['content_id'],
                    'click_hour': row['click_hour']
                })

            # 准备输出目录
            output_dir = '/data/gongzhijia/data/features'
            os.makedirs(output_dir, exist_ok=True)
            output_file = os.path.join(output_dir, 'user_features.csv')

            # 写入CSV文件
            with open(output_file, 'w', newline='', encoding='utf-8') as csvfile:
                fieldnames = [
                    'openid', 'deviceid', 'user_last_login_time', 'user_login_count_7d', 'user_is_login_3d',
                    'user_total_clicks', 'user_category_clicks',
                    'user_click_ts_diff_min', 'user_click_ts_diff_mean', 'user_click_ts_diff_std', 'user_click_ts_diff_max',
                    'user_click_ts_hour_mean',
                    'user_last_click_1', 'user_last_click_2', 'user_last_click_3', 'user_last_click_4', 'user_last_click_5'
                ]
                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
                writer.writeheader()

                # 处理每个用户的数据
                for (openid, deviceid), login_data in login_dict.items():
                    user_key = (openid, deviceid)
                    clicks = user_click_data.get(user_key, [])
                    user_data = {
                        'openid': openid,
                        'deviceid': deviceid,
                        'user_last_login_time': login_data['user_last_login_time'],
                        'user_login_count_7d': login_data['user_login_count_7d'],
                        'user_is_login_3d': login_data['user_is_login_3d'],
                        'user_total_clicks': len(clicks),
                        'user_category_clicks': len(set(click['content_id'] for click in clicks)) if clicks else 0,
                        'user_click_ts_diff_min': 0,
                        'user_click_ts_diff_mean': 0,
                        'user_click_ts_diff_std': 0,
                        'user_click_ts_diff_max': 0,
                        'user_click_ts_hour_mean': 0,
                        'user_last_click_1': '',
                        'user_last_click_2': '',
                        'user_last_click_3': '',
                        'user_last_click_4': '',
                        'user_last_click_5': ''
                    }

                    # 计算时间间隔指标
                    if len(clicks) >= 2:
                        time_diffs = []
                        for i in range(1, len(clicks)):
                            prev_time = clicks[i-1]['event_time']
                            curr_time = clicks[i]['event_time']
                            diff = (curr_time - prev_time).total_seconds()
                            time_diffs.append(diff)

                        user_data['user_click_ts_diff_min'] = min(time_diffs) if time_diffs else 0
                        user_data['user_click_ts_diff_mean'] = np.mean(time_diffs) if time_diffs else 0
                        user_data['user_click_ts_diff_std'] = np.std(time_diffs) if time_diffs else 0
                        user_data['user_click_ts_diff_max'] = max(time_diffs) if time_diffs else 0

                    # 计算平均点击小时
                    if clicks:
                        hours = [click['click_hour'] for click in clicks]
                        user_data['user_click_ts_hour_mean'] = np.mean(hours) if hours else 0

                    # 获取最近五次点击
                    recent_clicks = clicks[-5:][::-1]  # 取最后5个并反转顺序
                    for i, click in enumerate(recent_clicks):
                        if i < 5:
                            user_data[f'user_last_click_{i+1}'] = click['content_id'] if click['content_id'] else ''

                    writer.writerow(user_data)

            # 打印统计的用户数量
            print(f"共统计了 {len(login_dict)} 位用户的数据")
            print(f"用户特征数据已保存至: {output_file}")
            return output_file

    finally:
        connection.close()


if __name__ == '__main__':
    get_user_features()

